package sisi.invoices;

import java.util.List;

import javax.persistence.EntityManager;

import sisi.General;
import sisi.cash.Cash;

public class CustinvoiceController{

	private EntityManager getEntityManager() {
		return new sisi.MyEmf().getEntityManager();
	}

	public Custinvoice[] get(Integer numOrder, Integer idCustomer) {
		List<Custinvoice> lista = getList(numOrder, idCustomer);
			return lista.toArray(new Custinvoice[0]);
		}

	@SuppressWarnings("unchecked")
	public List<Custinvoice> getList(Integer numOrder, Integer idCustomer) {

		String cWhere = "";
		cWhere = " WHERE c.order="+numOrder+" AND c.custid="+idCustomer;

		EntityManager em = getEntityManager();
		try {
			javax.persistence.Query q = em
				.createQuery("select c from Custinvoice as c " + cWhere 
					+ " order by c.recnum ");

			List<Custinvoice> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}


	@SuppressWarnings("unchecked")
	public List<Custinvoice> getList(Integer order) {

		EntityManager em = getEntityManager();
		try {
			javax.persistence.Query q = em
				.createQuery("select c from Custinvoice as c " + (order!=null?" WHERE c.order=:order " : "" )+  " order by c.order" );
			
			if (order!=null){
				q.setParameter("order", order);
			}
			List<Custinvoice> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}

	
	public int getCountEqualsEnqNumber(Integer numorder) {

		EntityManager em = getEntityManager();
		try {
			String cString = "select count(c) from Custinvoice c ";
			
				cString += " WHERE ";			
				cString += " c.order = "+numorder; 
						
			javax.persistence.Query q = em
					.createQuery(cString);
						
			Long result = ((Long) q.getSingleResult());
			int quantirecords = result.intValue();
			return quantirecords;
		} finally {
			em.close();
		}
	}
	
	public int getCountForInvoiceNumberAndYear(Integer invoiceNumber, Integer year) {

		EntityManager em = getEntityManager();
		try {
			String cString = "select count(c) from Custinvoice c ";
			
				cString += " WHERE ";			
				cString += " c.custnuminvoice = "+invoiceNumber+" AND SQL('EXTRACT(YEAR FROM ?)', c.invoicedt ) = "+year; 
						
			javax.persistence.Query q = em
					.createQuery(cString);
						
			Long result = ((Long) q.getSingleResult());
			int quantirecords = result.intValue();
			return quantirecords;
		} finally {
			em.close();
		}
	}
	
	
	public Custinvoice getxId(int id) {

		EntityManager em = getEntityManager();
		try {
			Custinvoice custinvoicex = em.find(Custinvoice.class, id);
			return custinvoicex;
		} finally {
			em.close();
		}
	}

	@SuppressWarnings("finally")
	public Custinvoice add(Custinvoice custinvoice) {
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			em.persist(custinvoice);
			em.getTransaction().commit();
			custinvoice = refresh(custinvoice);
		} finally {
			em.close();
			return custinvoice;
		}
	}

	@SuppressWarnings("finally")
	public boolean update(Custinvoice custinvoice) {
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			Custinvoice custinvoicex = em.find(Custinvoice.class, custinvoice.getRecnum());
			custinvoicex.setType(custinvoice.getType());
			custinvoicex.setNote(custinvoice.getNote());
			custinvoicex.setOrder(custinvoice.getOrder());
			custinvoicex.setRecnum(custinvoice.getRecnum());
			custinvoicex.setArt15(custinvoice.getArt15());
			custinvoicex.setBankdoc(custinvoice.getBankdoc());
			custinvoicex.setBankdtdoc(custinvoice.getBankdtdoc());
			custinvoicex.setCcType(custinvoice.getCcType());
			custinvoicex.setCfId(custinvoice.getCfId());
			custinvoicex.setChange(custinvoice.getChange());
			custinvoicex.setClipobject(custinvoice.getClipobject());
			custinvoicex.setCn(custinvoice.getCn());
			custinvoicex.setCreditterms(custinvoice.getCreditterms());
			custinvoicex.setCurr(custinvoice.getCurr());
			custinvoicex.setCustid(custinvoice.getCustid());
			custinvoicex.setCustname(custinvoice.getCustname());
			custinvoicex.setCustnuminvoice(custinvoice.getCustnuminvoice());
			custinvoicex.setDeleted(custinvoice.getDeleted());
			custinvoicex.setDeliverydt(custinvoice.getDeliverydt());
			custinvoicex.setDtUp(custinvoice.getDtUp());
			custinvoicex.setDuedate(custinvoice.getDuedate());
			custinvoicex.setExchangerate(custinvoice.getExchangerate());
			custinvoicex.setFuel(custinvoice.getFuel());
			custinvoicex.setGroupcode(custinvoice.getGroupcode());
			custinvoicex.setInitial(custinvoice.getInitial());
			custinvoicex.setInvoicedt(custinvoice.getInvoicedt());
			custinvoicex.setInvoicedtreg(custinvoice.getInvoicedtreg());
			custinvoicex.setMemento(custinvoice.getMemento());
			custinvoicex.setNfysNote(custinvoice.getNfysNote());
			custinvoicex.setPIva(custinvoice.getPIva());
			custinvoicex.setPaidamount(custinvoice.getPaidamount());
			custinvoicex.setPaydt(custinvoice.getPaydt());
			custinvoicex.setPaymode(custinvoice.getPaymode());
			custinvoicex.setPortid(custinvoice.getPortid());
			custinvoicex.setPorttoref(custinvoice.getPorttoref());
			custinvoicex.setRa(custinvoice.getRa());
			custinvoicex.setStat(custinvoice.getStat());
			custinvoicex.setSuppid(custinvoice.getSuppid());
			custinvoicex.setSuppname(custinvoice.getSuppname());
			custinvoicex.setTotalinvoice(custinvoice.getTotalinvoice());
			custinvoicex.setTotaltaxedvat(custinvoice.getTotaltaxedvat());
			custinvoicex.setTotalvat(custinvoice.getTotalvat());
			custinvoicex.setVatarticle(custinvoice.getVatarticle());
			custinvoicex.setVatyesno(custinvoice.getVatyesno());
			em.getTransaction().commit();
			em.flush();

		} finally {
			em.close();
		return false;
		}
	}

@SuppressWarnings("finally")
public Custinvoice refresh(Custinvoice custinvoice) {

		Custinvoice custinvoicex = custinvoice;
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			custinvoicex = em.find(Custinvoice.class, custinvoice.getRecnum());
			em.flush();
			em.refresh(custinvoicex);
		} finally {
			em.close();
			return custinvoicex;
		}
}

@SuppressWarnings("finally")
public boolean remove(Custinvoice custinvoice) {
		EntityManager em = getEntityManager();
		try {
			em.getTransaction().begin();
			Custinvoice custinvoicex = em.find(Custinvoice.class, custinvoice.getRecnum());
			em.remove(custinvoicex);
			em.getTransaction().commit();
		} finally {
			em.close();
			return false;
		}
}





	// funzioni per calcolare i totali per il brogliaccio

	@SuppressWarnings("unchecked")
	public List<Custinvoice> getList1(Integer numRef) {
	
		EntityManager em = getEntityManager();
		try {
			
			javax.persistence.Query q = em
				.createQuery("select c from Custinvoice as c WHERE c.order= " + numRef + " AND UPPER( c.stat ) <> 'HI' ");
			List<Custinvoice> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}

	@SuppressWarnings("unchecked")
	public List<Suppinvoice> getList2(Integer numRef) {
		
		EntityManager em = getEntityManager();
		try {
			
			javax.persistence.Query q = em
				.createQuery("select c from Suppinvoice as c WHERE c.order= " + numRef + " AND UPPER( c.stat ) <> 'HI' ");
			List<Suppinvoice> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}

	@SuppressWarnings("unchecked")
	public List<Custinvoice> getList3(Integer numRef) {
		
		EntityManager em = getEntityManager();
		try {
			
			javax.persistence.Query q = em
				.createQuery("select c from Custinvoice as c WHERE c.order= " + numRef + " AND UPPER( c.stat ) = 'PD' ");
			List<Custinvoice> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}

	@SuppressWarnings("unchecked")
	public List<Suppinvoice> getList4(Integer numRef) {
		
		EntityManager em = getEntityManager();
		try {
			
			javax.persistence.Query q = em
				.createQuery("select c from Suppinvoice as c WHERE c.order= " + numRef + " AND UPPER( c.stat ) = 'PD' ");
			List<Suppinvoice> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Cash> getList5or6(Integer numRef, String type) {
		
		EntityManager em = getEntityManager();
		try {
			// type == A or P
			javax.persistence.Query q = em
				.createQuery("select c from Cash as c WHERE c.ref= '" + numRef + "' AND UPPER( c.doctype ) = '"+type+"' ");
			List<Cash> lista = q.getResultList();
			return lista;
		} finally {
			em.close();
		}
	}
	
	
	public Double BrogliaccioSum(Integer numRef, int opzione){
		Double somma = 0.00;
		
		if (opzione==1){
			List<Custinvoice> listcustinvoice = getList1(numRef);
			for (Custinvoice custinvoice : listcustinvoice) {
				Double exchrate = custinvoice.getExchangerate();
				exchrate = (exchrate==null || exchrate.compareTo( Double.valueOf(0) )==0 ? Double.valueOf(1) : exchrate );
				somma = somma + custinvoice.getTotalinvoice() / exchrate;
			}
		} else if (opzione==2){
			List<Suppinvoice> listsuppinvoice = getList2(numRef);
			for (Suppinvoice suppinvoice : listsuppinvoice) {
				Double exchrate = suppinvoice.getExchangerate();
				exchrate = (exchrate==null || exchrate.compareTo( Double.valueOf(0) )==0 ? Double.valueOf(1) : exchrate );
				somma = somma + suppinvoice.getTottaxed() / exchrate;
			}
		} else if (opzione==3){
			List<Custinvoice> listcustinvoice = getList3(numRef);
			for (Custinvoice custinvoice : listcustinvoice) {
				Double exchrate = custinvoice.getChange();
				exchrate = (exchrate==null || exchrate.compareTo( Double.valueOf(0) )==0 ? Double.valueOf(1) : exchrate );
				somma = somma + custinvoice.getTotalinvoice() / exchrate;
			}
		} else if (opzione==4){
			List<Suppinvoice> listsuppinvoice = getList4(numRef);
			for (Suppinvoice suppinvoice : listsuppinvoice) {
				Double exchrate = suppinvoice.getChange();
				exchrate = (exchrate==null || exchrate.compareTo( Double.valueOf(0) )==0 ? Double.valueOf(1) : exchrate );
				somma = somma + suppinvoice.getTottaxed() / exchrate;
			}
		} else if (opzione==5 || opzione==6){
			List<Cash> listcash = getList5or6(numRef, (opzione==5 ? "A" : "P" ) );
			for (Cash cash : listcash) {
				Double exchrate = cash.getFxRate();
				exchrate = (exchrate==null || exchrate.compareTo( Double.valueOf(0) )==0 ? Double.valueOf(1) : exchrate );
				Double wt_amount = General.doubleZeroNull( cash.getWtAmount() );
				Double fi_amount = General.doubleZeroNull( cash.getFiAmount() );
				Double cc_amount = General.doubleZeroNull( cash.getCcPayAmount() );
				Double ch_amount = General.doubleZeroNull( cash.getChPayAmount() );
				Double rb_amount = General.doubleZeroNull( cash.getRbAmount() );
				Double cs_amount = General.doubleZeroNull( cash.getCsAmount() );
				
				Double tmpSomma = wt_amount + fi_amount + cc_amount + ch_amount + rb_amount + cs_amount;
				tmpSomma = tmpSomma / exchrate;
				
				somma += tmpSomma;

			}
		}
		
		
		return somma;
	}
	
	
	public Double getSommaGeneratedQuery( String cQuery ) {

		EntityManager em = getEntityManager();
		try {
			String cString = cQuery;
						
			javax.persistence.Query q = em
					.createQuery(cString);
						
			Double result = ((Double) q.getSingleResult());
			return General.doubleZeroNull( result );
		} finally {
			em.close();
		}
	}
	
	
	
}
//